package org.qiancizhan.Mapper;

import org.qiancizhan.Config.MySQL;
import org.qiancizhan.Model.POJO.UserPOJO;
import org.qiancizhan.Model.POJO.VocabularyListPOJO;
import org.qiancizhan.Utils.ThreadLocalUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class VocabularyMapper {
    private final MySQL mysql = MySQL.getInstance();

    public List<VocabularyListPOJO> getVocabularyList() {
        return mysql.executeQuery("SELECT * FROM vocabulary_list", this::mapVocabularyList);
    }

    public List<VocabularyListPOJO> getUserVocabularyList() {
        UserPOJO user = ThreadLocalUtil.get();

        return mysql.executeQuery(String.format("SELECT vocabulary_list.* FROM user_vocabulary_list uv " +
                "JOIN users ON users.id = uv.fk_users_id " +
                "JOIN vocabulary_list ON vocabulary_list.id = uv.fk_vocabulary_list_id " +
                "WHERE users.id = %d", user.getId()), this::mapVocabularyList);
    }

    public VocabularyListPOJO getVocabularyUserListById(int vocabulary_list_id) {
        UserPOJO user = ThreadLocalUtil.get();
        List<VocabularyListPOJO> data = mysql.executeQuery(String.format("SELECT vl.* FROM user_vocabulary_list uv JOIN vocabulary_list vl ON uv.fk_vocabulary_list_id = vl.id WHERE uv.fk_users_id = %d AND uv.fk_vocabulary_list_id = %d", user.getId(), vocabulary_list_id), this::mapVocabularyList);
        return data.size() == 0 ? null : data.get(0);
    }

    public boolean addUserVocabularyList(int vocabulary_list_id) {
        UserPOJO user = ThreadLocalUtil.get();
        return mysql.executeUpdate(
                "INSERT INTO user_vocabulary_list (fk_users_id, fk_vocabulary_list_id) VALUES (?, ?)",
                user.getId(),
                vocabulary_list_id
        ) > 0;
    }

    private VocabularyListPOJO mapVocabularyList(ResultSet rs) throws SQLException {
        VocabularyListPOJO pojo = new VocabularyListPOJO();
        pojo.setId(rs.getInt("id"));
        pojo.setTable_name(rs.getString("table_name"));
        pojo.setBook_name(rs.getString("book_name"));
        pojo.setWord_count(rs.getInt("word_count"));
        pojo.setDescription(rs.getString("description"));
        pojo.setCreated_at(rs.getTimestamp("created_at"));
        pojo.setUpdated_at(rs.getTimestamp("updated_at"));
        return pojo;
    }

    public boolean deleteUserVocabularyList(int vocabularyListId) {
        UserPOJO user = ThreadLocalUtil.get();
        return mysql.executeUpdate(
                "DELETE FROM user_vocabulary_list WHERE fk_users_id = ? AND fk_vocabulary_list_id = ?",
                user.getId(),
                vocabularyListId
        ) > 0;
    }
}